﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_ImportQuotation : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;

        public panel_ImportQuotation()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();

            this.ImportQuotation.DoubleClick += ImportQuotation_DoubleClick;
            this.ImportQuotation.PopupMenuShowing += ImportQuotation_PopupMenuShowing;
            this.importQuotationContext.ItemClicked += importQuotationContext_ItemClicked;
        }

        private void callEditForm()
        { 
            DataRow row = ImportQuotation.GetFocusedDataRow();
            frm_Edit_ImportQuotation editForm = new frm_Edit_ImportQuotation();
            editForm.partno = row[2].ToString();
            editForm.supplierid = row[10].ToString();
            if (editForm.LoadData())
            {
                //editForm.Show();
                editForm.ShowDialog();
                //LoadData();
            }
            else
            {
                MessageBox.Show("Cannot load data for selected record");
            } 
        }

        void ImportQuotation_DoubleClick(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        void importQuotationContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.importQuotationContext.Hide();            
            switch (e.ClickedItem.Name)
            {
                case "importSelection":
                    importDatabase();
                    break;
                case "addSelection":
                    frm_Add_ImportQuotation add_form = new frm_Add_ImportQuotation();
                    add_form.ShowDialog();
                    //LoadData();
                    break;
                case "editSelection":
                    callEditForm();                    
                    break;
                case "deleteSelection":
                    deleteRecord();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void ImportQuotation_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.importQuotationContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT t1.[ID], t1.[Date], t1.[PartNo], t1.[Quantity], t1.[Price], t5.[UnitNameEN], t1.[CurrencyID],"
                + " t2.[PartNoPurchase], t2.[PartEnglishName], t2.[PartVietNamName], t1.[SupplierID], t4.[SupplierName],"
                + " t3.[EnglishName], t1.[Note]"
                + " FROM [241_ImportQuotation] AS t1 LEFT OUTER JOIN"
                + " [222_Part] AS t2 ON t2.[PartNo]=t1.[PartNo] LEFT OUTER JOIN"
                + " [222_Maker] AS t3 ON t3.[MakerID]=t2.[MakerID] LEFT OUTER JOIN"
                + " [222_Supplier] AS t4 ON t4.[SupplierID]=t1.[SupplierID] LEFT OUTER JOIN"
                + " [222_Unit] AS t5 ON t5.[UnitID]=t2.[BuyingUnit]"
                + " WHERE t1.[Old] = 0"
                + " ORDER BY t1.[Date] DESC";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridImportQuotation.DataSource = sourceDataSet.Tables[0];                
                getUpdateInfo();
            }
            else
            {
                MessageBox.Show("There is no data for Import Quotation");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("Date", typeof(string));
                newTable.Columns.Add("PartNo", typeof(int));
                newTable.Columns.Add("Quantity", typeof(string));
                newTable.Columns.Add("Price", typeof(string));
                newTable.Columns.Add("Unit", typeof(string));
                newTable.Columns.Add("Currency", typeof(string));
                newTable.Columns.Add("PartNoPurchase", typeof(string));
                newTable.Columns.Add("PartEnglishName", typeof(string));
                newTable.Columns.Add("PartVietNamName", typeof(string));
                newTable.Columns.Add("SupplierID", typeof(string));
                newTable.Columns.Add("Supplier", typeof(string));
                newTable.Columns.Add("Maker", typeof(string));
                newTable.Columns.Add("Note", typeof(string));
                gridImportQuotation.DataSource = newTable;
            }
            ImportQuotation.Columns[0].Caption = "ID";
            ImportQuotation.Columns[1].Caption = "Date";
            ImportQuotation.Columns[2].Caption = "Part No";
            ImportQuotation.Columns[3].Caption = "Quantity";
            ImportQuotation.Columns[4].Caption = "Price";
            ImportQuotation.Columns[5].Caption = "Unit";
            ImportQuotation.Columns[6].Caption = "Currency";
            ImportQuotation.Columns[7].Caption = "Part No Purchase";
            ImportQuotation.Columns[8].Caption = "Part English Name";
            ImportQuotation.Columns[9].Caption = "Part VietNam Name";
            ImportQuotation.Columns[10].Caption = "SupplierID";
            ImportQuotation.Columns[11].Caption = "Supplier";
            ImportQuotation.Columns[12].Caption = "Maker";           
            ImportQuotation.Columns[13].Caption = "Note";
            ImportQuotation.Columns[0].Visible = false;
            ImportQuotation.Columns[10].Visible = false;
            ImportQuotation.BestFitColumns();
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=17";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }
        
        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////

        private void importDatabase()
        {
            DataSet as400 = new DataSet();
            DataSet importquotation = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable importquotation_db2;
            DataTable importquotation_local;

            int change_price = 0;
            int change_quantity = 0;
            int add_record = 0;

            try
            {
                //Dictionary<string, string> symbol = new Dictionary<string, string>() {{"B", "THB"}, {"W", "KRW"}, {"D", "VND"},
                //    {"E", "EUR"}};
                Dictionary<string, string> currencyid = new Dictionary<string, string>() { { "B", "THB" }, { "W", "KRW" },
                    { "D", "VND" }, { "E", "EUR" }, {"\\", "JPY"}, {"$", "USD"} };

                //Get Data From AS400                
                masterConnection.Open();
                sql = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT t1.MITDTE AS Date, t1.MIHNBN AS PartNo, t1.MISICD SupplierID,"
                    + " t1.MISRYO AS Quantity, t1.MITANK AS Price, SUBSTRING(t2.GAFILL,43,1) AS Currency"
                    + " FROM MPMI00 AS t1 LEFT JOIN MPGA00 AS t2 ON t1.MISICD=t2.GASICD"
                    + "	WHERE (t1.MISICD >= ''B00010'') AND (t1.MISICD <= ''B99999'')')";
                
                cmd = new SqlCommand(sql, masterConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(as400);
                importquotation_db2 = as400.Tables[0];
                adapter.Dispose();
                cmd.Dispose();
                masterConnection.Close(); 
                
                //Get Data From Local Database
                myConnection.Open();
                sql = "SELECT * FROM [241_ImportQuotation] WHERE Old=0";
                cmd = new SqlCommand(sql, myConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(importquotation, "241_ImportQuotation");
                importquotation_local = importquotation.Tables["241_ImportQuotation"];

                foreach (DataRow as400_row in importquotation_db2.Rows)
                {
                    DataRow[] change_price_rows = importquotation_local.Select("PartNo='" + as400_row["PartNo"].ToString() + "'"
                        + " AND SupplierID='" + as400_row["SupplierID"].ToString() + "'"
                        + " AND Quantity = " + as400_row["Quantity"].ToString()
                        + " AND Price <> " + as400_row["Price"].ToString());
                    if (change_price_rows.Length > 0) // Change Price
                    {
                        foreach (DataRow local_row in change_price_rows)
                        {                            
                            local_row.BeginEdit();
                            local_row["Old"] = true;
                            local_row.EndEdit();

                            DataRow new_row = importquotation_local.NewRow();
                            new_row["Date"] = as400_row["Date"].ToString();
                            new_row["PartNo"] = as400_row["PartNo"].ToString();
                            new_row["SupplierID"] = as400_row["SupplierID"].ToString();
                            int quantity = 0;
                            if (Int32.TryParse(as400_row["Quantity"].ToString(), out quantity))
                            {
                                new_row["Quantity"] = quantity;
                            }
                            float price = 0;
                            if (float.TryParse(as400_row["Price"].ToString(), out price))
                            {
                                new_row["Price"] = Math.Round(price, 2);
                            }
                            string currency = null;
                            if (currencyid.ContainsKey(as400_row["Currency"].ToString()))
                            {
                                currency = currencyid[as400_row["Currency"].ToString()];
                            }
                            else
                            {
                                currency = null;
                            }
                            new_row["CurrencyID"] = currency;
                            importquotation_local.Rows.Add(new_row);

                            change_price++;
                        }
                    }
                    else //not change price or change quantity or new
                    {
                        DataRow[] not_change_rows = importquotation_local.Select("PartNo='" + as400_row["PartNo"].ToString() + "'"
                            + " AND SupplierID='" + as400_row["SupplierID"].ToString() + "'"
                            + " AND Quantity = " + as400_row["Quantity"].ToString()
                            + " AND Price = " + as400_row["Price"].ToString());
                        if (not_change_rows.Length <= 0) //change quantity or new
                        {
                            DataRow[] change_quantity_rows = importquotation_local.Select("PartNo='" + as400_row["PartNo"].ToString() + "'"
                                + " AND SupplierID='" + as400_row["SupplierID"].ToString()+ "'"
                                + " AND Quantity <> " + as400_row["Quantity"].ToString());
                            if (change_quantity_rows.Length > 0) //Change quantity
                            {
                                foreach (DataRow local_row in change_quantity_rows)
                                {
                                    local_row.BeginEdit();
                                    local_row["ChangeQuantity"] = true;
                                    local_row.EndEdit();

                                    change_quantity++;
                                }

                                DataRow new_row = importquotation_local.NewRow();
                                new_row["Date"] = as400_row["Date"].ToString();
                                new_row["PartNo"] = as400_row["PartNo"].ToString();
                                new_row["SupplierID"] = as400_row["SupplierID"].ToString();
                                int quantity = 0;
                                if (Int32.TryParse(as400_row["Quantity"].ToString(), out quantity))
                                {
                                    new_row["Quantity"] = quantity;
                                }
                                float price = 0;
                                if (float.TryParse(as400_row["Price"].ToString(), out price))
                                {
                                    new_row["Price"] = Math.Round(price, 2);
                                }
                                string currency = null;
                                if (currencyid.ContainsKey(as400_row["Currency"].ToString()))
                                {
                                    currency = currencyid[as400_row["Currency"].ToString()];
                                }
                                else
                                {
                                    currency = null;
                                }
                                new_row["CurrencyID"] = currency;
                                new_row["ChangeQuantity"] = true;
                                importquotation_local.Rows.Add(new_row);
                            }
                            else //new quotation
                            {
                                DataRow new_row = importquotation_local.NewRow();
                                new_row["Date"] = as400_row["Date"].ToString();
                                new_row["PartNo"] = as400_row["PartNo"].ToString();
                                new_row["SupplierID"] = as400_row["SupplierID"].ToString();
                                int quantity = 0;
                                if (Int32.TryParse(as400_row["Quantity"].ToString(), out quantity))
                                {
                                    new_row["Quantity"] = quantity;
                                }
                                float price = 0;
                                if (float.TryParse(as400_row["Price"].ToString(), out price))
                                {
                                    new_row["Price"] = Math.Round(price, 2);
                                }
                                string currency = null;
                                if (currencyid.ContainsKey(as400_row["Currency"].ToString()))
                                {
                                    currency = currencyid[as400_row["Currency"].ToString()];
                                }
                                else
                                {
                                    currency = null;
                                }
                                new_row["CurrencyID"] = currency;
                                //new_row["ChangeQuantity"] = true;
                                importquotation_local.Rows.Add(new_row);

                                add_record++;
                            }
                        }
                    }
                }  
                               

                SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                //Update to Local Database
                adapter.Update(importquotation, "241_ImportQuotation");
                
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
                
                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=17";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);                
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();

                if (change_quantity > 0)
                {
                    MessageBox.Show("There are " + change_quantity + " quotation has been changed quantity. Please update.");
                    frm_ChangeQuantity_ImportQuotation change_form = new frm_ChangeQuantity_ImportQuotation();
                    change_form.ShowDialog();                    
                }                
                string message = change_price + " quotations has been changed price.\n"
                    + change_quantity + " quotations has been changed quantity.\n"
                    + add_record + " new quotations has been added.\n\n"
                    + "Database has been updated successfull.";
                MessageBox.Show(message);
                LoadData();                                
            }
            catch //(Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }

                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                //MessageBox.Show(ex.Message);
                MessageBox.Show("Cannot update database from AS400");
            }
        }

        private void deleteRecord()
        {
            if (MessageBox.Show("Are you really to delete selected record now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                DataRow row = ImportQuotation.GetFocusedDataRow();
                try
                {
                    myConnection.Open();
                    string sql = "UPDATE [241_ImportQuotation] SET Old=1 WHERE ID=" + row[0].ToString();
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("Quotation deleted success.");
                    LoadData();
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot delete Quotation. Please try again.");
                }
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteRecord();            
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_ImportQuotation add_form = new frm_Add_ImportQuotation();
            add_form.ShowDialog();
            //LoadData();
        }

        private void buttonExportExcel_Click(object sender, EventArgs e)
        {
            Cursor currentCursor;
            currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                SaveFileDialog saveFDlg = new SaveFileDialog();
                saveFDlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                saveFDlg.Filter = "Microsoft Office Excel|*.xlsx";
                saveFDlg.Title = "Save As";
                saveFDlg.FileName = "ImportQuotation.xlsx";
                if (saveFDlg.ShowDialog(this) == DialogResult.OK)
                {
                    if (!string.IsNullOrEmpty(saveFDlg.FileName))
                    {
                        ImportQuotation.OptionsPrint.PrintDetails = true;
                        ImportQuotation.OptionsPrint.ExpandAllDetails = true;
                        ImportQuotation.OptionsPrint.AutoWidth = true;
                        ImportQuotation.ExportToXlsx(saveFDlg.FileName);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Warning!");
            }
            finally
            {
                Cursor.Current = currentCursor;
            }
        }
    }
}
